#/usr/sbin/ 
#-*- coding: utf-8 -*-
import requests
import pymysql
import socket
import json
import os
from threading import Thread 
#import urllib2
# import theano
# import theano.tensor as T
# from theano.ifelse import ifelse
# import numpy as np
# from random import random
# from theano import function

db = pymysql.connect('localhost','root','123456','shares',charset="utf8")
cursor = db.cursor()
cursor.execute('select COLUMN_NAME from information_schema.COLUMNS where table_name ="sharestable"')
data=cursor.fetchall()
l=[]
#print(list(list(data)[1]))
for dval in list(data):
	l.append(str(dval)[2:-3])
print(len(l))


def get_shares_code():
	page=1
	page = str(page)
	url='http://quotes.money.163.com/hs/service/diyrank.php?host=http%3A%2F%2Fquotes.money.163.com%2Fhs%2Fservice%2Fdiyrank.php&page='+page+'&query=STYPE%3AEQA&fields=NO%2CSYMBOL%2CNAME%2CPRICE%2CPERCENT%2CUPDOWN%2CFIVE_MINUTE%2COPEN%2CYESTCLOSE%2CHIGH%2CLOW%2CVOLUME%2CTURNOVER%2CHS%2CLB%2CWB%2CZF%2CPE%2CMCAP%2CTCAP%2CMFSUM%2CMFRATIO.MFRATIO2%2CMFRATIO.MFRATIO10%2CSNAME%2CCODE%2CANNOUNMT%2CUVSNEWS&sort=PERCENT&order=desc&count=24&type=query';
	headers=''
	req = requests.get(url,headers)
	req.encodinng='urf-8'
	content=req.text
	jkey=json.loads(content)
	data=jkey['list']
	sql='insert into sharestable('
	val=''
	kstr=''
	
	i=0
	j=0
	
	pages=1
	pagecount=jkey['pagecount']
	
	print(pagecount)
	print(data)
	exit()
	while pages <= pagecount:
		url='http://quotes.money.163.com/hs/service/diyrank.php?host=http%3A%2F%2Fquotes.money.163.com%2Fhs%2Fservice%2Fdiyrank.php&page='+str(pages)+'&query=STYPE%3AEQA&fields=NO%2CSYMBOL%2CNAME%2CPRICE%2CPERCENT%2CUPDOWN%2CFIVE_MINUTE%2COPEN%2CYESTCLOSE%2CHIGH%2CLOW%2CVOLUME%2CTURNOVER%2CHS%2CLB%2CWB%2CZF%2CPE%2CMCAP%2CTCAP%2CMFSUM%2CMFRATIO.MFRATIO2%2CMFRATIO.MFRATIO10%2CSNAME%2CCODE%2CANNOUNMT%2CUVSNEWS&sort=PERCENT&order=desc&count=24&type=query';
		headers=''
		req = requests.get(url,headers)
		req.encodinng='urf-8'
		content=req.text
		jkey=json.loads(content)
		data=jkey['list']
		print(data,'\r\n\r\n')
		continue
		#exit()
		invals=[]
		if data != None:
			for kval in data:
				vstr=''
				inval=[]
				for k,v in kval.items(): 
					if k.lower() in l:
						if i < 1:
							kstr+=''+k.lower()+','
						if (k=='NAME' or k=='SNAME'):
							vstr+='"'+str(v)+'",'
							inval.append(str(v))
						else:
							vstr+='"'+str(v)+'",'
							inval.append(str(v))
					if k == 'MFRATIO':
						inval.append(str(v['MFRATIO2']))
						inval.append(str(v['MFRATIO10']))
						vstr+=''+str(v['MFRATIO2'])+','
						vstr+=''+str(v['MFRATIO10'])+','
						if i<1:
							kstr+=''+'mfratio2'.lower()+','
							kstr+=''+'mfratio10'.lower()+','
				i=i+1
				tmp='('+vstr[:-1]+'),'
				val+=tmp
				invals.append(inval)
			sql+=kstr[:-1]+')'+' VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' #+val[:-1]
			try:
				cursor.executemany(sql,invals)
				db.commit()
				print('true')
			except:
				db.rollback()
				print('false')
			
			# f=open('sql.txt','w');
			# print(sql,file=f)
			# f.close()
			#db = pymysql.connect('localhost','root','123456','shares')
			#cursor = db.cursor()
			# result=''
		pages += 1
		print('\r\n\r\n')
get_shares_code()
db.close()








# create table ecs_shares(
	# `id` int(11) UNSIGNED AUTO_INCREMENT not null primary key comment 'id',
	# `code` int(11) not null default 0 comment '代码',
	# `five_minute` int(11) not null default 0 comment 'id',
	# `high` float(16,2) not null default 0.00 comment '最高',
	# `hs` double(20,16) not null default 0.00 comment '换手',
	# `lb` double(20,16) not null default 0.00 comment '量比',
	# `low` float(16,2) not null default 0.00 comment '最低',
	# `mcap` int(11) not null default 0 comment '流通市值',
	# `mfratio_yl` float(16,2) not null default 0.00 comment '净利润',
	# `mfratio_zys` float(16,2) not null default 0.00 comment '主营收',
	# `mfsum` float(16,2) not null default 0.00 comment '每股收益',
	# `name` varchar(128) not null default '' comment '名称',
	# `OPEN` float(16,2) not null default 0.00 comment '今日开盘加',
	# `pe` float(20,15) not null default 0.00 comment '市盈率',
	# `percent` float(16,10) not null default 0.00 comment '涨跌幅',
	# `price` float(16,2) not null default 0.00 comment '价格',
	# `sname` varchar(128) not null default '' comment '名称',
	# `symbol` int(11) not null default 0 comment '代码',
	# `tcap` int(11) not null default 0 comment '总市值',
	# `turnover` float(16,2) not null default 0.00 comment '成交额',
	# `updown` float(16,2) not null default 0.00 comment '涨跌幅',
	# `volune` int(15) not null default 0 comment '成交量',
	# `wb` float(20,15) not null default 0.00 comment '委比',
	# `yestclose` float(16,2) not null default 0.00 comment '昨日收盘价',
	# `zf` float(20,16) not null default 0.00 comment '振幅',
	# `no` int(11) not null default 0 comment '序号'
# )engine=myisam default charset=utf8;

# create table caiwu(
		# `id` int(11) UNSIGNED AUTO_INCREMENT not null primary key comment 'id',
		# yearend_date date not null comment "报表日期 ",
		# eps float(15,2) not null comment "基本每股收益 ",
		# eps_diluted float(15,2) not null comment "摊薄每股收益 ",
		# gross_margin float(15,2) not null comment "毛利率 ",
		# roloans float(15,2) not null comment "贷款回报率 ",
		# rota float(15,2) not null comment "总资产收益率 ",
		# roequity float(15,2) not null comment "净资产收益率 ",
		# current_ratio float(15,2) not null comment "流动比率 ",
		# quick_ratio float(15,2) not null comment "速动比率 ",
		# capital_adequacy float(15,2) not null comment "资产负债率",
		# total_asset2turnover float(15,2) not null comment "资本充足率 ",
		# loans_deposits float(15,2) not null comment "资产周转率 ",
		# inventory_turnover float(15,2) not null comment "存贷比 ",
		# general_admin_ratio float(15,2) not null comment "存货周转率 ",
		# fincosts_grossprofit float(15,2) not null comment "管理费用比率 ",
		# turnover_cash float(15,2) not null comment "财务费用比率 "
		# )engine=myisam default charset=utf8;

		# create table lirun(
		# `id` int(11) UNSIGNED AUTO_INCREMENT not null primary key comment 'id',
		# yearend_date date not null comment "报告日期 ",
		# turnover float(15,2) not null comment "总营收 ",
		# income_interest float(15,2) not null comment "利息收益 ",
		# income_netfee float(15,2) not null comment "费用收益 ",
		# income_nettrading float(15,2) not null comment "交易收益 ",
		# oper_profit float(15,2) not null comment "经营利润 ",
		# pbt float(15,2) not null comment "除税前利润 ",
		# net_prof float(15,2) not null comment "净利润 ",
		# eps float(15,2) not null comment "每股基本盈利 ",
		# dps float(15,2) not null comment "每股派息 "
		# )engine=myisam default charset=utf8;

		# create table fuzai(
		# `id` int(11) UNSIGNED AUTO_INCREMENT not null primary key comment 'id',
		# yearend_date date not null comment "报表日期 ",
		# fix_ass float(15,2) not null comment "固定资产 ",
		# curr_ass float(15,2) not null comment "流动资产 ",
		# curr_liab float(15,2) not null comment "流动负债 ",
		# inventory float(15,2) not null comment "存款 ",
		# cash float(15,2) not null comment "现金及银行存结 ",
		# other_ass float(15,2) not null comment "其他资产 ",
		# cash_shorttermfund float(15,2) not null comment "库存现金及短期资金 ",
		# deposits_from_customer float(15,2) not null comment "客户存款 ",
		# loan_to_bank float(15,2) not null comment "银行同业存款及贷款 ",
		# financialasset_sale float(15,2) not null comment "可供出售之证券 ",
		# derivatives_asset float(15,2) not null comment "金融资产 ",
		# derivatives_liabilities float(15,2) not null comment "金融负债 ",
		# total_ass float(15,2) not null comment "总资产 ",
		# total_debt float(15,2) not null comment " ",
		# total_liab float(15,2) not null comment "总负债",
		# equity float(15,2) not null comment "股东权益 "
		# )engine=myisam default charset=utf8;

		# create table xianjinliu(
		# `id` int(11) UNSIGNED AUTO_INCREMENT not null primary key comment 'id',
		# yearend_date date not null comment "报表日期 ",
		# cf_ncf_operact float(15,2) not null comment "经营活动产生的现金流 ",
		# cf_int_rec float(15,2) not null comment "已收利息 ",
		# cf_int_paid float(15,2) not null comment "已付利息 ",
		# cf_div_rec float(15,2) not null comment "已收股息 ",
		# cf_div_paid float(15,2) not null comment "已派股息 ",
		# cf_inv float(15,2) not null comment "投资活动产生现金流 ",
		# cf_fin_act float(15,2) not null comment "融资活动产生现金流 ",
		# cf_beg float(15,2) not null comment "期初现金及现金等价物 ",
		# cf_change_csh float(15,2) not null comment "现金及现金等价物净增加额 ",
		# cf_end float(15,2) not null comment "期末现金及现金等价物 ",
		# cf_exch float(15,2) not null comment "汇率变动影响 "
		# )engine=myisam default charset=utf8;

		
		
		
#!/usr/bin/env python 
#create table log ( logline varchar(300)); 
#grant all on pythondata.* to 'pyuser'@'localhost' identified by "pypasswd"  
# import MySQLdb 
# import os 
# import sys 
# from time import ctime 
# from threading import Thread 
# from Queue import Queue 
 
# num_thread=10 
# queue=Queue() 
 
# def listDir(path): 
        # file_list=[] 
        # for filename in os.listdir(path): 
                # file_list.append(os.path.join(path,filename)) 
        # return file_list 
 
# def readFile(file): 
        # alllines=[] 
        # for line in open(file): 
                # alllines.append(line) 
        # return alllines 
 
# def writeLinestoDb(q,queue): 
        # sql=insertsql ="INSERT INTO log(logline) VALUES(%s)" 
        # while True: 
                # content=queue.get() 
                # conn=MySQLdb.connect(host="localhost",user="pyuser",passwd="pypasswd",db="pythondata") 
                # cur =conn.cursor() 
                # cur.executemany(sql,content) 
                # cur.close() 
                # conn.commit() 
                # conn.close() 
                # queue.task_done() 
 
# def main(): 
        # print "Readfile Start at,",ctime() 
        # for file in listDir('/data/logs/testlog/20120219/testlog1'): 
                # queue.put(readFile(file)) 
        # print "Readfile Done at,",ctime() 
        # for q in range(num_thread): 
                # worker=Thread(target=writeLinestoDb,args=(q,queue)) 
                # worker.setDaemon(True) 
                # worker.start() 
        # print "Insert into mysql Main Thread at",ctime() 
        # queue.join() 
        # print "Insert into mysql at,",ctime() 
# if __name__=="__main__": 
        # print('starting at:',ctime()) 
        # main() 
        # print('ending at:',ctime()) 		
		